package cn.com.nes.site.module.dao.monitor;

import cn.com.nes.common.util.DBUtils;
import cn.com.nes.common.util.Globals;
import cn.com.nes.common.util.ParameterUtil;
import cn.com.nes.common.util.XzqhUtil;
import cn.com.nes.site.entity.DataReq;
import cn.com.nes.site.entity.PageData;
import cn.com.nes.site.module.dao.BaseDaoImpl;
import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.util.StrUtil;
import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.TransactionStatus;

import java.math.BigDecimal;
import java.sql.Connection;
import java.util.*;

@Repository
public class MapDaoImpl extends BaseDaoImpl {

	private TransactionStatus newg_status = null;

	public PageData getProjectLocation(DataReq req) {
		PageData res = new PageData();
		HashMap<String, Object> param = req.getReqData();
		Map<String,Object> extData = new HashMap<String, Object>();
		StringBuffer sb = new StringBuffer();
		try {

			//获取项目数量列表
			//判断行政区划下是否有项目
			String ifStr = "0";
			String areaCode = req.getUinfo().getAreacode();
			if(StringUtils.isEmpty((String) param.get("xzqhdm"))) {
				if(!StringUtils.isEmpty(areaCode)) {
					String[] xzqhdmList = areaCode.split(";",-1) ;
					ifStr = "if(";
					for(int i=0;i<xzqhdmList.length;i++) {
						if(i!=0) {
							ifStr = " or ";
						}
						ifStr +=" a.area_code='" +xzqhdmList[i] + "' ";
					}
					ifStr +=",1,0)";
				}
			}else {
				ifStr = "if(a.area_code='"+ param.get("xzqhdm") +"',1,0)";
			}
			sb.delete(0, sb.length()) ;
			sb.append("select c.xzqhdm,c.xzqhmc,c.xzqhjb,a.jd,a.wd,count(a.uuid) projectCount,count(b.id) deviceCount,a.flag\r\n"
					+ " from (\r\n"
					+ "	select a.uuid,a.company_id,b.xzqhdm,b.xzqhmc, "
					+ " if(a.longitude is not null,a.longitude,b.zxjd) jd, "
					+ " if(a.latitude is not null,a.latitude,b.zxwd) wd, "
					+ ifStr + " flag"
					+ "	from t_project a \r\n"
					+ "	left join t_xzqh b on b.xzqhdm=a.area_code\r\n"
					+ "	where a.company_id='").append(req.getUinfo().getCompanyId()).append("' ");
			if(StringUtils.isEmpty((String) param.get("xzqhdm"))) {
				   sb.append(XzqhUtil.getMultipleQueryXzqhConn(getDBConnect_newg(), req.getUinfo().getAreacode(),"b.xzqhdm",new String[] {}));
			}else {
				sb.append(" and b.xzqhdm like '").append(XzqhUtil.getQueryXzqh(getJdbcTemplate_newg(), (String) param.get("xzqhdm"))).append("%'");
			}
			sb.append("	) a	left join nes_ope_prod.is_equipment_info b on a.uuid=b.dd_id\r\n");
			//left join
			String query = "a.xzqhdm";
			if(!StringUtils.isEmpty((String) param.get("xzqhdm")) && !"86".equals(param.get("xzqhdm"))) {
				String xzqhjb = XzqhUtil.getXzqhjb(getDBConnect_newg(), (String) param.get("xzqhdm"));
				if(xzqhjb.compareTo("1")>0) {
					query = XzqhUtil.f_get_dwonQueryXzqh_report("a.xzqhdm", xzqhjb);
				}
			}else {
				query = "concat(substr(a.xzqhdm,1,2),'')";
			}

			//group by
			String query1 = "c.xzqhdm";
			if(!StringUtils.isEmpty((String) param.get("xzqhdm"))) {
				String xzqhjb = XzqhUtil.getXzqhjb(getDBConnect_newg(), (String) param.get("xzqhdm"));
				if(xzqhjb.compareTo("1")>=0) {
					query1 = XzqhUtil.f_get_dwonQueryXzqh_report("c.xzqhdm", xzqhjb);
				}
			}
			sb.append("	left join t_xzqh c on c.xzqhdm=" + query
					+ "	group by ").append(query1);
			System.out.println(sb.toString());
			List<Map<String, Object>> list = this.getJdbcTemplate_newg()
					.queryForList(sb.toString(),
							new Object[] {});

			for(int i=0;i<list.size();i++) {
				double[] lnglat = new double[2];
				BigDecimal jd = (BigDecimal) list.get(i).get("jd");
				BigDecimal wd = (BigDecimal) list.get(i).get("wd");
				lnglat[0] = jd.doubleValue();
				lnglat[1] = wd.doubleValue();
				list.get(i).put("lnglat", lnglat);
			}
			extData.put("projectCountList", list);

			//获取项目分散
			List<Map<String, Object>> projectFsList = new ArrayList<Map<String,Object>>();
			sb.delete(0, sb.length());
			for(int i=0;i<list.size();i++) {
				Integer flag = (Integer) list.get(i).get("flag");
				String xzqhdm = (String) list.get(i).get("xzqhdm");
				if(flag==1) {
					sb.append("select a.uuid,a.project_name,a.contact,a.contact_phone,a.project_address,\r\n"
							+ "	IF\r\n"
							+ "		( a.longitude IS NOT NULL, a.longitude, b.zxjd ) jd,\r\n"
							+ "	IF\r\n"
							+ "		( a.latitude IS NOT NULL, a.latitude, b.zxwd ) wd \r\n"
							+ "	from t_project a\r\n"
							+ "	left join t_xzqh b on b.xzqhdm=a.area_code\r\n"
							+ "	where a.company_id='").append(req.getUinfo().getCompanyId()).append("' and b.xzqhdm='").append(xzqhdm).append("' ");
					List<Map<String, Object>> fsList = this.getJdbcTemplate_newg().queryForList(sb.toString(),new Object[] {});
					for(int j=0;j<fsList.size();j++) {
						double[] lnglat = new double[2];
						BigDecimal jd = (BigDecimal) fsList.get(j).get("jd");
						BigDecimal wd = (BigDecimal) fsList.get(j).get("wd");
						lnglat[0] = jd.doubleValue();
						lnglat[1] = wd.doubleValue();
						fsList.get(i).put("lnglat", lnglat);
					}
					projectFsList.addAll(fsList);
				}
			}
			extData.put("projectFsList", projectFsList);

			res.setExtData(extData);
			res.setRetCode(0);
			res.setRetMessage("OK");
		} catch (Exception e) {
			e.printStackTrace();

		} finally {

		}
		return res;
	}

	public PageData getDeviceCount(DataReq req) {
		PageData res = new PageData();
		HashMap<String, Object> param = req.getReqData();
		StringBuffer sb = new StringBuffer();
		StringBuffer sbfrom = new StringBuffer();
		try {
			sb.append("select a.sb_id as sbid,b.type as lb,if(d.contact is not null and d.contact!='',d.contact,b.xmlxr) as hzxm,"
					+ " if(d.project_address is not null and d.project_address!='',d.project_address,b.xmdz) as xxdz,"
					+ " a.jd,a.wd,b.mph,a.dd_id as ddId,"
					+ "           (CASE\r\n"
					+ "				WHEN a.wx_id IS NULL THEN 0\r\n"
					+ "				ELSE 1\r\n"
					+ "				END\r\n"
					+ "            )  isBinding,FLOOR(c.a350) AS sbzt,c.is_online as wxsbzt,\r\n"
					+ "			  (CASE\r\n"
					+ "				WHEN a.wx_id IS NULL THEN NULL\r\n"
					+ "				WHEN c.is_warn = 1 AND c.fault_code IS NULL THEN 1\r\n"
					+ "				WHEN c.fault_code != '' THEN 2\r\n"
					+ "				ELSE 0\r\n"
					+ "				END\r\n"
					+ "            ) yxzt,c.gmt_create as enddate,if(a.is_master=1,1,0) isPic,b.jd as xmjd,b.wd as xmwd,tx.zxjd as xzjd,tx.zxwd as xzwd\r\n"
					+ " from nes_ope_prod.is_equipment_info a \r\n"
					+ " INNER JOIN nes_ope_prod.is_install_info b ON a.dd_id = b.dd_id\r\n"
					+ " LEFT JOIN nes_ope_prod.is_norm_data_b c ON c.sb_id = a.sb_id\r\n"
					+ " LEFT JOIN nes_ope_prod.t_xzqh tx ON tx.xzqhdm = b.xzqh\r\n"
					+ " left join t_project d on a.dd_id=d.uuid\r\n"
					+ " where 1=1 ");
			if("01".equals(req.getUinfo().getCompanyType())) { //厂商
				sb.append(" and a.manufacture_id='").append(req.getUinfo().getNes_companyId()).append("' ");
			}else if("02".equals(req.getUinfo().getCompanyType())){
				sb.append(" and a.agent_id='").append(req.getUinfo().getCompanyId()).append("' ");
			}else {
				sb.append(" and 1=0 ");
			}
			sb.append(" and ((a.is_master = 1 AND EXISTS(SELECT 1\r\n"
					+ "      FROM nes_ope_prod.is_device_item idi\r\n"
					+ "      WHERE idi.is_deleted = 0\r\n"
					+ "      AND idi.sb_id = a.sb_id))\r\n"
					+ "      OR a.is_master IS NULL)\r\n"
					+ "      AND a.sfsc = 0\r\n"
					+ "      AND b.sfsc = 0\r\n"
					+ "      AND (a.sbyt !='workorder' or a.sbyt is null)");


			sbfrom.append(sb.toString());
			System.out.println(sb.toString());
			List<Map<String, Object>> list = this.getJdbcTemplate_newg()
					.queryForList(sb.toString(),
							new Object[] {});
			Iterator<Map<String, Object>> iterator = list.iterator();
			while(iterator.hasNext()) {
				Map<String, Object> next = iterator.next();
				if( next.get("xzjd")==null || next.get("xzwd")==null
						|| Math.abs(((BigDecimal) next.get("xzjd")).doubleValue())<1
						|| Math.abs(((BigDecimal) next.get("xzwd")).doubleValue())<1) {
					iterator.remove();
					continue;
				}
				double jd = ((BigDecimal) next.get("xzjd")).doubleValue();
				double wd = ((BigDecimal) next.get("xzwd")).doubleValue();
				if(next.get("jd")!=null && next.get("wd")!=null) {
					jd = ((BigDecimal) next.get("jd")).doubleValue();
					wd = ((BigDecimal) next.get("wd")).doubleValue();
				}else if(next.get("xmjd")!=null && next.get("xmwd")!=null) {
					jd = ((BigDecimal) next.get("xmjd")).doubleValue();
					wd = ((BigDecimal) next.get("xmwd")).doubleValue();
				}
				Double[] lnglat = new Double[2];
	            lnglat[0] = jd;
	            lnglat[1] = wd;
	            next.put("lnglat", lnglat);

	          //判断设备使用类别
	            String lb = String.valueOf(next.get("lb"));
	            if (!StringUtils.isEmpty(lb)) {
	                if ("1".equals(lb)) {
	                    next.put("lb", "商用");
	                } else if ("2".equals(lb)) {
	                    next.put("lb","户用");
	                }
	            }
			}

			res.setExtData(list);
			res.setRetCode(0);
			res.setRetMessage("OK");
		} catch (Exception e) {
			e.printStackTrace();

		} finally {

		}
		return res;
	}

	public PageData getCountOfDeviceGroupByArea(DataReq req) throws Exception {
		PageData res = new PageData();
		res.setRetCode(9);
		res.setRetMessage("查询设备数量失败");
		String xzqhdm = (String) req.getReqData().get("xzqhdm");
		String userxzqhdm = req.getUinfo().getAreacode();
		if (StringUtils.isEmpty(xzqhdm)) {
			if (!StringUtils.isEmpty(userxzqhdm)) {
				xzqhdm = userxzqhdm;
			}
		}
		Connection conn = null;
		try {
			conn = DBUtils.getJdbcForOpe();
			// 查询 总安装数量
			StringBuffer sb = new StringBuffer();
			sb.append(" select count(iei.sb_id) installCount,count(ind2.sb_id) onlineCount,count(iei.sb_id) - count(ind2.sb_id) offlineCount, " +
                    " ifnull(tx.xzqhmc, '未知地区') xzqhmc, tx.xzqhdm, tx.zxjd jd, tx.zxwd wd, tx.xzqhjb");
			sb.append("  from nes_ope_prod.is_equipment_info iei ");
			sb.append("           inner join nes_ope_prod.is_install_info iii on (iii.dd_id = iei.dd_id and iii.sfsc = 0)                                           ");
			sb.append("           left join nes_ope_prod.is_device_protocol_info b on b.protocol_id = iei.protocol_id                                               ");
			sb.append("           left join nes_ope_prod.t_sblx ts on (ts.sblx_id = iei.sblx_id and ts.sfsc = 0)                                                    ");
			sb.append("           left join nes_ope_prod.is_dict_value_info idvi on idvi.type_id = 'd_type_2' and idvi.dic_no = ts.sblx                             ");
			sb.append("           left join nes_ope_prod.is_norm_data_b ind on ind.dtu_id = iei.wx_id and ind.sb_id = iei.sb_id                                     ");
			sb.append("       left join nes_ope_prod.is_norm_data ind2 on ind2.dtu_id = iei.wx_id and ind2.sb_id = iei.sb_id and ind2.is_online = 0                 ");
			sb.append("       left join nes_ope_prod.is_user_info iui on iui.user_id = iei.create_oper                                                              ");
			String query = "iii.xzqh";
			//group by
			String query1 = "tx.xzqhdm";
			String xzqhjb = "0";
			if (!StringUtils.isEmpty(xzqhdm) && !"86".equals(xzqhdm)) {
				xzqhjb = XzqhUtil.getXzqhjb(conn, xzqhdm);
				if (StringUtils.isNotEmpty(xzqhjb)) {
					query = XzqhUtil.f_get_dwonQueryXzqh_report("iii.xzqh", xzqhjb);
					query1 = XzqhUtil.f_get_dwonQueryXzqh_report("tx.xzqhdm", xzqhjb);
				}

			} else {
				query = "concat(substr(iii.xzqh,1,2),'')";
			}
			sb.append("       left join nes_ope_prod.t_xzqh tx on tx.xzqhdm = " + query);
			sb.append("       where iei.sfsc = 0 and iei.sfzj != 2  ");
			sb.append(XzqhUtil.getMultipleQueryXzqhConn(conn, userxzqhdm, "iii.xzqh"));
			String companyType = req.getUinfo().getCompanyType();
			if (!Globals.DEMO_COMPANYID.equals(req.getUinfo().getCompanyId())) {//不是演示账号
				if ("01".equals(companyType)) {//厂商
					sb.append(" and iei.manufacture_id= '").append(req.getUinfo().getNes_companyId()).append("' ");
				} else if ("02".equals(companyType)) {//代理商
					sb.append(" and iei.agent_id= '").append(req.getUinfo().getCompanyId()).append("' ");
				} else {//管理员
					sb.append(" and iei.manufacture_id='MA3NU7QX1' ");
				}
			} else {//演示账号默认德尚
				sb.append(" and iei.manufacture_id='MA3NU7QX1' ");
			}

			String ismap = (String) req.getReqData().get("ismap");
			if ("1".equals(ismap)) {
				sb.append(" and iii.jd is not null and  iii.wd is not null ");
			}

			String is_master = (String) req.getReqData().get("is_master");
			if (!StringUtils.isEmpty(is_master)) {
				if ("1".equals(is_master)) {
					sb.append(" and iei.is_master= 1 ");
				} else {
					sb.append(" and (iei.is_master is null or iei.is_master <> 1 ) ");
				}
			}

			if (!StringUtils.isEmpty((String) req.getReqData().get("xzqhdm")) && !"86".equals((String) req.getReqData().get("xzqhdm"))) {
				sb.append(XzqhUtil.getMultipleQueryXzqhConn(conn, (String) req.getReqData().get("xzqhdm"), "iii.xzqh"));
			}
			if (StringUtils.isNotEmpty(xzqhjb)) {
				sb.append(" and tx.xzqhjb >" + xzqhjb);
			}
			//如果加条件参考getEquipment方法

			sb.append(" group by " + query1);
			sb = DBUtils.sql_check(sb);
			List<Map<String, Object>> list = this.queryForList(conn, sb.toString(), new Object[]{});
			//转换经纬度
			for (int i = 0; i < list.size(); i++) {
				List<Object> lnglat = new ArrayList<Object>();
				lnglat.add(list.get(i).get("jd"));
				lnglat.add(list.get(i).get("wd"));
				list.get(i).put("lnglat", lnglat);
			}
			res.setPage(list);
			res.setRetCode(0);
			res.setRetMessage("OK");
		} finally {
			if (conn != null) {
				conn.close();
			}
		}
		return res;
	}

	public PageData getCountAndRateOfDeviceGroupByType(DataReq req) throws Exception {
		PageData res = new PageData();
		res.setRetCode(9);
		res.setRetMessage("查询设备数量失败");
		StringBuffer sb = new StringBuffer();
		Connection conn = null;
		try {
			conn = DBUtils.getJNDIDBConn();
			HashMap<String, Object> retMap = new HashMap<>();

			sb.append("select count(iei.sb_id) as total, count(ind2.sb_id) online, count(ind2.sb_id)/count(iei.sb_id) as onlineRate  " +
                    " from nes_ope_prod.is_equipment_info iei \n" +
					" inner join nes_ope_prod.is_install_info iii ON ( iii.dd_id = iei.dd_id AND iii.sfsc = 0 )"+
					" left join nes_ope_prod.is_norm_data ind2 on ind2.dtu_id = iei.wx_id and ind2.sb_id = iei.sb_id and ind2.is_online = 0");
			sb.append(" where iei.sfsc = 0 ");
			// 用户信息中获取用户的公司类型
			String companyType = req.getUinfo().getCompanyType();
			// 根据公司类型、公司ID，查询设备数据
			if (!Globals.DEMO_COMPANYID.equals(req.getUinfo().getCompanyId())) {//不是演示账号
				if ("01".equals(companyType)) {//厂商
					sb.append(" and iei.manufacture_id= '").append(req.getUinfo().getNes_companyId()).append("' ");
				} else if ("02".equals(companyType)) {//代理商
					sb.append(" and iei.agent_id= '").append(req.getUinfo().getCompanyId()).append("' ");
				} else {//管理员
					sb.append(" and iei.manufacture_id='MA3NU7QX1' ");
				}
			} else {//演示账号默认德尚
				sb.append(" and iei.manufacture_id='MA3NU7QX1' ");
			}
			List<Map<String, Object>> list = queryForList(conn, sb.toString(), new Object[]{});
			if (CollUtil.isNotEmpty(list)) {
				Integer total = Integer.valueOf(String.valueOf(list.get(0).get("total")));
				Integer online = Integer.valueOf(String.valueOf(list.get(0).get("online")));
				BigDecimal onlineRate1 = (BigDecimal) list.get(0).get("onlineRate");
				Double onlineRate = onlineRate1.setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue() * 100;
				String format = String.format("%.2f", onlineRate);
				onlineRate = Double.parseDouble(format);
				if (onlineRate == 0) {
					retMap.put("onlineRate", 0);
				} else {
					retMap.put("onlineRate", onlineRate);
				}
				retMap.put("total", total);
				retMap.put("online", online);

				sb.delete(0, sb.length());
				sb.append("select count(iei.sb_id) totalCount, count(ind2.sb_id) onlineCount, count(ind2.sb_id)/count(iei.sb_id) as onlineRate\n"
						+ " from nes_ope_prod.is_equipment_info iei\n"
						+ " left join nes_ope_prod.is_install_info iii on iii.dd_id = iei.dd_id \n"
						+ " left join nes_ope_prod.is_norm_data ind2 on ind2.dtu_id = iei.wx_id and ind2.sb_id = iei.sb_id and ind2.is_online = 0\n" + " where iei.sfsc = 0 and iii.type = 1\n");
				if (!Globals.DEMO_COMPANYID.equals(req.getUinfo().getCompanyId())) {//不是演示账号
					if ("01".equals(companyType)) {//厂商
						sb.append(" and iei.manufacture_id= '").append(req.getUinfo().getNes_companyId()).append("' ");
					} else if ("02".equals(companyType)) {//代理商
						sb.append(" and iei.agent_id= '").append(req.getUinfo().getNes_companyId()).append("' ");
					} else {//管理员
						sb.append(" and iei.manufacture_id='MA3NU7QX1' ");
					}
				} else {//演示账号默认德尚
					sb.append(" and iei.manufacture_id='MA3NU7QX1' ");
				}
				List<Map<String, Object>> list1 = queryForList(conn, sb.toString(), new Object[]{});
				if (CollUtil.isNotEmpty(list1)) {
					Integer businessTotal = Integer.valueOf(String.valueOf(list1.get(0).get("totalCount")));
					Integer businessOnline = Integer.valueOf(String.valueOf(list1.get(0).get("onlineCount")));
					Double businessOnlineRate = ((BigDecimal) list1.get(0).get("onlineRate")).setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue() * 100;
					String format1 = String.format("%.2f", businessOnlineRate);
					businessOnlineRate = Double.parseDouble(format1);
					if (businessOnlineRate == 0) {
						retMap.put("businessOnlineRate", 0);
					} else {
						retMap.put("businessOnlineRate", businessOnlineRate);
					}
					retMap.put("businessTotal", businessTotal);
					retMap.put("businessOnline", businessOnline);


					Integer userTotal = total - businessTotal;
					Integer userOnline = online - businessOnline;
					Double userOnlineRate = 0 == userTotal ? 0D : new BigDecimal((float) userOnline / userTotal).setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue() * 100;
					String format2 = String.format("%.2f", userOnlineRate);
					userOnlineRate = Double.parseDouble(format2);
					if (userOnlineRate == 0) {
						retMap.put("userOnlineRate", 0);
					} else {
						retMap.put("userOnlineRate", userOnlineRate);
					}
					retMap.put("userTotal", userTotal);
					retMap.put("userOnline", userOnline);
				}
			}
			res.setData(retMap);
			res.setRetCode(0);
			res.setRetMessage("OK");
		} finally {
			if (conn != null) {
				conn.close();
			}
		}
		return res;
	}

	public PageData getNewDevice(DataReq req) throws Exception {
		PageData res = new PageData();
		res.setRetCode(9);
		res.setRetMessage("查询最新上线设备失败");
		StringBuffer sb = new StringBuffer();
		Connection conn = null;
		try {
			conn = DBUtils.getDBConn_Ope();
			sb.append("select iei.wx_id as dtuId, substring(d.xzqhqc, 1, 2) as area, date_format(iei.cjsj, '%Y-%m-%d') as onlineTime, if(b.is_online = 0, '在线', '离线') as online\n" +
					"from is_equipment_info iei\n" +
					"inner join is_norm_data b on b.dtu_id = iei.wx_id and b.sb_id = iei.sb_id\n" +
					"left join is_install_info c on c.dd_id = iei.dd_id\n" +
					"left join t_xzqh d on d.xzqhdm = c.xzqh\n" +
					"where iei.sfsc = 0");
			// 用户信息中获取用户的公司类型
			String companyType = req.getUinfo().getCompanyType();
			if (!Globals.DEMO_COMPANYID.equals(req.getUinfo().getCompanyId())) {//不是演示账号
				if ("01".equals(companyType)) {//厂商
					sb.append(" and iei.manufacture_id= '").append(req.getUinfo().getNes_companyId()).append("' ");
				} else if ("02".equals(companyType)) {//代理商
					sb.append(" and iei.agent_id= '").append(req.getUinfo().getCompanyId()).append("' ");
				} else {//管理员
					sb.append(" and iei.manufacture_id='MA3NU7QX1' ");
				}
			} else {//演示账号默认德尚
				sb.append(" and iei.manufacture_id='MA3NU7QX1' ");
			}
			sb.append("order by iei.cjsj desc limit 20");
			List<Map<String, Object>> list = queryForList(conn, sb.toString(), new Object[]{});
			res.setPage(list);
			res.setRetCode(0);
			res.setRetMessage("OK");
		} finally {
			if (conn != null) {
				conn.close();
			}
		}
		return res;
	}

	public PageData getDeviceSaleTrendGroupByDeviceModel(DataReq req) throws Exception {
		PageData res = new PageData();
		res.setRetCode(9);
		res.setRetMessage("查询设备型号销售趋势失败");
		StringBuffer sb = new StringBuffer();
		Connection conn = null;
		try {
			conn = DBUtils.getDBConn_Ope();
			sb.append("select ts.sbxh, count(iei.sblx_id) as num\n" +
					"from is_equipment_info iei\n" +
					"inner join t_sblx ts on ts.sblx_id = iei.sblx_id\n" +
					"where iei.sfsc = 0");
			// 用户信息中获取用户的公司类型
			String companyType = req.getUinfo().getCompanyType();
			if (!Globals.DEMO_COMPANYID.equals(req.getUinfo().getCompanyId())) {//不是演示账号
				if ("01".equals(companyType)) {//厂商
					sb.append(" and iei.manufacture_id= '").append(req.getUinfo().getNes_companyId()).append("' ");
				} else if ("02".equals(companyType)) {//代理商
					sb.append(" and iei.agent_id= '").append(req.getUinfo().getCompanyId()).append("' ");
				} else {//管理员
					sb.append(" and iei.manufacture_id='MA3NU7QX1' ");
				}
			} else {//演示账号默认德尚
				sb.append(" and iei.manufacture_id='MA3NU7QX1' ");
			}
			sb.append("group by iei.sblx_id order by count(iei.sblx_id) desc");
			List<Map<String, Object>> list = queryForList(conn, sb.toString(), new Object[]{});
			res.setPage(list);
			res.setRetCode(0);
			res.setRetMessage("OK");
		} finally {
			if (conn != null) {
				conn.close();
			}
		}
		return res;
	}

	public PageData getAgentSaleTrendByTime(DataReq req) throws Exception {
		PageData res = new PageData();
		res.setRetCode(9);
		res.setRetMessage("查询代理商数据失败");
		StringBuffer sb = new StringBuffer();
		Connection conn = null;
		try {
			conn = DBUtils.getJNDIDBConn();
			String companyType = req.getUinfo().getCompanyType();
			sb.append("select tc.companyName, tc.shortname \n from t_agent_facturer taf\n");
			// 01-厂商，02-代理商
			if (companyType.equals("01")) {
				sb.append(" inner join t_company tc on tc.companyId = taf.agent_id\n");
				sb.append(" where taf.deleted = 0 and taf.facturer_id = '" + req.getUinfo().getCompanyId() + "'");
			} else {
				sb.append(" inner join t_company tc on tc.companyId = taf.facturer_id\n");
				sb.append(" where taf.deleted = 0 and taf.agent_id = '" + req.getUinfo().getCompanyId() + "'");
			}
			List<Map<String, Object>> list = queryForList(conn, sb.toString(), new Object[]{});
			if (CollUtil.isNotEmpty(list)) {
				// 将数据转换为map， 方便后续取值、赋值
				HashMap<String, Long> agentMap = new HashMap<>();
				for (Map<String, Object> map : list) {
					agentMap.put((String) map.get("shortname"), 0L);
				}

				// 查询代理商的设备数量
				sb.delete(0, sb.length());
				sb.append("SELECT b.shortname as companyName, count(iei.sb_id) as deviceCount \n from nes_ope_prod.is_equipment_info iei \n");
				if (companyType.equals("01")) {
					sb.append("INNER JOIN t_company b on iei.agent_id=b.companyId\n" +
							"where iei.sfsc = 0 and iei.manufacture_id='" + req.getUinfo().getNes_companyId() + "'\n");
				} else {
					sb.append("INNER JOIN t_company b on iei.manufacture_id = b.nes_companyId\n" +
							"where iei.sfsc = 0 and iei.agent_id = '" + req.getUinfo().getCompanyId() + "'\n");
				}
				// timeType 默认0 0-本周，1-本月，2-本季，3-本年
				Integer timeType = ParameterUtil.getInteger(req, "timeType", 0);
				switch (timeType) {
					case 0:
						sb.append(" and year(iei.cjsj) = year(now())" +
								" and MONTH(iei.cjsj) = MONTH(now())\n" +
								" and week(iei.cjsj) = week(now()) ");
						break;
					case 1:
						sb.append(" and year(iei.cjsj) = year(now())\n" +
								" and MONTH(iei.cjsj) = MONTH(now()) \n");
						break;
					case 2:
						sb.append(" and year(iei.cjsj) = year(now())\n" +
								" and quarter(iei.cjsj) = quarter(now()) \n");
						break;
					case 3:
						sb.append(" and year(iei.cjsj) = year(now())\n");
						break;
				}
				sb.append(" group by b.shortname ");
				List<Map<String, Object>> list1 = queryForList(conn, sb.toString(), new Object[]{});
				if (CollUtil.isNotEmpty(list1)) {
					for (Map<String, Object> map : list1) {
						agentMap.put((String) map.get("companyName"), (Long) map.get("deviceCount"));
					}
				}
				HashMap<String, Object> retMap = new HashMap<>();
				retMap.put("agents", agentMap.keySet());
				List<Long> deviceCounts = new ArrayList<>();
				for (String s : agentMap.keySet()) {
					deviceCounts.add(agentMap.get(s));
				}
				retMap.put("deviceCount", deviceCounts);
				res.setData(retMap);
			} else {
				res.setRetMessage("无数据");
				return res;
			}

			res.setRetCode(0);
			res.setRetMessage("OK");
		} finally {
			if (conn != null) {
				conn.close();
			}
		}
		return res;
	}

	public PageData getDeviceTypeSaleTrendGroupByMonth(DataReq req) throws Exception {
		PageData res = new PageData();
		res.setRetCode(9);
		res.setRetMessage("查询销售趋势失败");
		StringBuffer sb = new StringBuffer();
		Connection conn = null;
		try {
			conn = DBUtils.getDBConn_Ope();
			sb.append("select MONTH(iei.cjsj) month, COUNT(iii.type = 1 or null) as sy, COUNT(iii.type != 1 or null) as hy\n" +
					"from is_equipment_info iei\n" +
					"inner join is_install_info iii on iii.dd_id = iei.dd_id\n" +
					"where iei.sfsc = 0\n" +
					"  and year(iei.cjsj) = year(now())");
			// 用户信息中获取用户的公司类型
			String companyType = req.getUinfo().getCompanyType();
			if (!Globals.DEMO_COMPANYID.equals(req.getUinfo().getCompanyId())) {//不是演示账号
				if ("01".equals(companyType)) {//厂商
					sb.append(" and iei.manufacture_id= '").append(req.getUinfo().getNes_companyId()).append("' ");
				} else if ("02".equals(companyType)) {//代理商
					sb.append(" and iei.agent_id= '").append(req.getUinfo().getCompanyId()).append("' ");
				} else {//管理员
					sb.append(" and iei.manufacture_id='MA3NU7QX1' ");
				}
			} else {//演示账号默认德尚
				sb.append(" and iei.manufacture_id='MA3NU7QX1' ");
			}
			sb.append("and iei.manufacture_id = 'MA3NU7QX1'\n" +
					"group by MONTH(iei.cjsj);");
			List<Map<String, Object>> list = queryForList(conn, sb.toString(), new Object[]{});
			Long[] sy = new Long[]{0L,0L,0L,0L,0L,0L,0L,0L,0L,0L,0L,0L};
			Long[] hy = new Long[]{0L,0L,0L,0L,0L,0L,0L,0L,0L,0L,0L,0L};
			if (CollUtil.isNotEmpty(list)) {
				for (Map<String, Object> map : list) {
					Integer month = (Integer) map.get("month");
					sy[month-1] = (Long) map.get("sy");
					hy[month-1] = (Long) map.get("hy");
				}
			}
			HashMap<String, Object> retMap = new HashMap<>();
			retMap.put("sy", sy);
			retMap.put("hy", hy);
			res.setData(retMap);
			res.setRetCode(0);
			res.setRetMessage("OK");
		} finally {
			if (conn != null) {
				conn.close();
			}
		}
		return res;
	}

	public PageData getFaultAndWarnForDeviceGroupByProvinceOnTime(DataReq req) throws Exception {
		PageData res = new PageData();
		res.setRetCode(9);
		res.setRetMessage("查询预警故障失败");
		StringBuffer sb = new StringBuffer();
		Connection conn = null;
		try {
			conn = DBUtils.getDBConn_Ope();

			// timeType 默认0 0-今日，1-本周，2-本月
			Integer timeType = ParameterUtil.getInteger(req, "timeType", 0);
			sb.append("select tx.xzqhmc\n" +
					"from is_equipment_info iei\n" +
					"inner join is_install_info iii on iii.dd_id = iei.dd_id\n" +
					"inner join t_xzqh tx on tx.xzqhdm = substr(iii.xzqh, 1,2) and tx.xzqhjb = 1\n" +
					"where iei.sfsc = 0 \n");
			// 用户信息中获取用户的公司类型
			String companyType = req.getUinfo().getCompanyType();
			if (!Globals.DEMO_COMPANYID.equals(req.getUinfo().getCompanyId())) {//不是演示账号
				if ("01".equals(companyType)) {//厂商
					sb.append(" and iei.manufacture_id= '").append(req.getUinfo().getNes_companyId()).append("' ");
				} else if ("02".equals(companyType)) {//代理商
					sb.append(" and iei.agent_id= '").append(req.getUinfo().getCompanyId()).append("' ");
				} else {//管理员
					sb.append(" and iei.manufacture_id='MA3NU7QX1' ");
				}
			} else {//演示账号默认德尚
				sb.append(" and iei.manufacture_id='MA3NU7QX1' ");
			}
			sb.append(" group by substr(iii.xzqh, 1,2)\n" +
					" order by substr(iii.xzqh, 1,2)");
			List<Map<String, Object>> provinces = queryForList(conn, sb.toString(), new Object[]{});
			Map<String, Long> faultsMap = new LinkedHashMap<>();
			Map<String, Long> warnsMap = new LinkedHashMap<>();
			if (CollUtil.isNotEmpty(provinces)) {
				for (Map<String, Object> province : provinces) {
					faultsMap.put((String) province.get("xzqhmc"), 0L);
					warnsMap.put((String) province.get("xzqhmc"), 0L);
				}
			}


			sb.delete(0, sb.length());
			sb.append("select tx.xzqhmc, count(iefi.sb_id) faultCount\n" +
					"from is_equipment_info iei\n" +
					"inner join is_install_info iii on iii.dd_id = iei.dd_id\n" +
					"left join is_equipment_fault_info iefi on iefi.sb_id = iei.sb_id\n" +
					"left join t_xzqh tx on tx.xzqhdm = substr(iii.xzqh, 1,2) and tx.xzqhjb = 1\n");
			sb.append("where iei.sfsc = 0 and YEAR(iefi.cjsj) = YEAR(now())");
			// 用户信息中获取用户的公司类型
			if (!Globals.DEMO_COMPANYID.equals(req.getUinfo().getCompanyId())) {//不是演示账号
				if ("01".equals(companyType)) {//厂商
					sb.append(" and iei.manufacture_id= '").append(req.getUinfo().getNes_companyId()).append("' ");
				} else if ("02".equals(companyType)) {//代理商
					sb.append(" and iei.agent_id= '").append(req.getUinfo().getCompanyId()).append("' ");
				} else {//管理员
					sb.append(" and iei.manufacture_id='MA3NU7QX1' ");
				}
			} else {//演示账号默认德尚
				sb.append(" and iei.manufacture_id='MA3NU7QX1' ");
			}
			switch (timeType) {
				case 0:
					sb.append(" and MONTH(iefi.cjsj) = MONTH(now())\n" +
							" and DAY(iefi.cjsj) = DAY(now()) ");
					break;
				case 1:
					sb.append(" and WEEK(iefi.cjsj) = WEEK(now()) ");

					break;
				case 2:
					sb.append(" and MONTH(iefi.cjsj) = MONTH(now()) ");
					break;
			}
			sb.append(" group by substr(iii.xzqh, 1,2)\n" +
					" order by substr(iii.xzqh, 1,2)");

			List<Map<String, Object>> faults = queryForList(conn, sb.toString(), new Object[]{});
			if (CollUtil.isNotEmpty(faults)) {
				for (Map<String, Object> fault : faults) {
					faultsMap.put((String) fault.get("xzqhmc"), (Long) fault.get("faultCount"));
				}
			}

			sb.delete(0, sb.length());
			sb.append("select tx.xzqhmc, count(iewi.sb_id) warnCount\n" +
					"from is_equipment_info iei\n" +
					"inner join is_install_info iii on iii.dd_id = iei.dd_id\n" +
					"left join is_equipment_warn_info iewi on iewi.sb_id = iei.sb_id\n" +
					"left join t_xzqh tx on tx.xzqhdm = substr(iii.xzqh, 1,2) and tx.xzqhjb = 1\n");
			sb.append("where iei.sfsc = 0 and YEAR(iewi.cjsj) = YEAR(now())");
			// 用户信息中获取用户的公司类型
			if (!Globals.DEMO_COMPANYID.equals(req.getUinfo().getCompanyId())) {//不是演示账号
				if ("01".equals(companyType)) {//厂商
					sb.append(" and iei.manufacture_id= '").append(req.getUinfo().getNes_companyId()).append("' ");
				} else if ("02".equals(companyType)) {//代理商
					sb.append(" and iei.agent_id= '").append(req.getUinfo().getCompanyId()).append("' ");
				} else {//管理员
					sb.append(" and iei.manufacture_id='MA3NU7QX1' ");
				}
			} else {//演示账号默认德尚
				sb.append(" and iei.manufacture_id='MA3NU7QX1' ");
			}
			switch (timeType) {
				case 0:
					sb.append(" and MONTH(iewi.cjsj) = MONTH(now())\n" +
							" and DAY(iewi.cjsj) = DAY(now()) ");
					break;
				case 1:
					sb.append(" and WEEK(iewi.cjsj) = WEEK(now()) ");
					break;
				case 2:
					sb.append(" and MONTH(iewi.cjsj) = MONTH(now()) ");
					break;
			}
			sb.append(" group by substr(iii.xzqh, 1,2)\n" +
					" order by substr(iii.xzqh, 1,2)");

			List<Map<String, Object>> warns = queryForList(conn, sb.toString(), new Object[]{});
			if (CollUtil.isNotEmpty(faults)) {
				for (Map<String, Object> warn : warns) {
					warnsMap.put((String) warn.get("xzqhmc"), (Long) warn.get("warnCount"));
				}
			}


			HashMap<String, Object> retMap = new HashMap<>();
			retMap.put("province", faultsMap.keySet());
			List<Long> faultList = new ArrayList<>();
			for (String s : faultsMap.keySet()) {
				faultList.add(faultsMap.get(s));
			}
			retMap.put("faults", faultList);
			List<Long> warnList = new ArrayList<>();
			for (String s : warnsMap.keySet()) {
				warnList.add(warnsMap.get(s));
			}
			retMap.put("warns", warnList);
			res.setData(retMap);
			res.setRetCode(0);
			res.setRetMessage("OK");
		} finally {
			if (conn != null) {
				conn.close();
			}
		}
		return res;
	}

	public PageData getDeviceRelateDataForMap(DataReq req) throws Exception {
		PageData res = new PageData();
		res.setRetCode(9);
		res.setRetMessage("查询设备相关数据失败");
		StringBuffer sb = new StringBuffer();
		Connection conn = null;
		Connection conn_ope = null;
		Map<String, Object> retMap = new HashMap<>();
		try {
			conn = DBUtils.getJNDIDBConn();
			conn_ope = DBUtils.getDBConn_Ope();
			// 查询用户所在公司的经纬度坐标
			sb.append("select tc.companyName, tx.xzqhmc, tc.latitude, tc.longitude\n" +
					"from t_company tc\n" +
					"inner join t_xzqh tx on tx.xzqhdm = tc.areacode\n" +
					"where tc.companyId = '" + req.getUinfo().getCompanyId() + "'");
			List<Map<String, Object>> list1 = queryForList(conn, sb.toString(), new Object[]{});
			if (CollUtil.isNotEmpty(list1)) {
				retMap.put("companyLocate", list1.get(0));
			}

			// 查询厂商设备所在的省份有哪些
			sb.delete(0, sb.length());
			sb.append("select substr(tx.xzqhdm, 1, 2) as areaCode, tx.xzqhmc as province, count(iei.sb_id) as deviceCount, tx.zxjd, tx.zxwd\n" +
					"from is_equipment_info iei\n" +
					"         inner join is_install_info iii on iii.dd_id = iei.dd_id\n" +
					"         inner join t_xzqh tx on tx.xzqhdm = substr(iii.xzqh, 1, 2)\n" +
					"where iei.sfsc = 0 ");
			// 用户信息中获取用户的公司类型
			String companyType = req.getUinfo().getCompanyType();
			if (!Globals.DEMO_COMPANYID.equals(req.getUinfo().getCompanyId())) {//不是演示账号
				if ("01".equals(companyType)) {//厂商
					sb.append(" and iei.manufacture_id= '").append(req.getUinfo().getNes_companyId()).append("' ");
				} else if ("02".equals(companyType)) {//代理商
					sb.append(" and iei.agent_id= '").append(req.getUinfo().getCompanyId()).append("' ");
				} else {//管理员
					sb.append(" and iei.manufacture_id='MA3NU7QX1' ");
				}
			} else {//演示账号默认德尚
				sb.append(" and iei.manufacture_id='MA3NU7QX1' ");
			}
			sb.append("group by substr(tx.xzqhdm, 1, 2)");
			List<Map<String, Object>> list = queryForList(conn_ope, sb.toString(), new Object[]{});
			retMap.put("procinces", list);

			String xzqhdm = ParameterUtil.getString(req, "xzqhdm");
			if (StrUtil.isNotEmpty(xzqhdm)) {
				xzqhdm = XzqhUtil.getQueryXzqh(conn, xzqhdm);
			}
			sb.delete(0, sb.length());
			sb.append("select tx.xzqhdm as areaCode, iei.sb_id as sbid, if(iei.alias is not null, iei.alias, if(ts.sbmc is not null, ts.sbmc, ts.sbxh)) as deviceName, " +
					"idpi.protocol_id as protocalId," +
					" if(ind.is_online = 0, if(ind.fault_code != '', 2, if(ind.is_warn = 1, 3, 1)), 4) as deviceStatus," + //1 正常，2故障，3预警，4离线
					"if(iei.jd is null, tx.zxjd, iei.jd) as zxjd, if(iei.wd is null, tx.zxwd, iei.wd) as zxwd \n" +
					"from is_equipment_info iei\n" +
					" inner join is_install_info iii on iii.dd_id = iei.dd_id\n" +
					" inner join t_xzqh tx on tx.xzqhdm = iii.xzqh\n" +
					" left join is_wxsb iw on iw.wx_id = iei.wx_id\n" +
					" left join is_device_protocol_info idpi on idpi.protocol_id = iw.protocol_id\n" +
					" left join is_norm_data ind on ind.sb_id = iei.sb_id and ind.dtu_id = iei.wx_id" +
					" left join t_sblx ts on ts.sblx_id = iei.sblx_id \n" +
					"where iei.sfsc = 0 ");
			// 用户信息中获取用户的公司类型
			if (!Globals.DEMO_COMPANYID.equals(req.getUinfo().getCompanyId())) {//不是演示账号
				if ("01".equals(companyType)) {//厂商
					sb.append(" and iei.manufacture_id= '").append(req.getUinfo().getNes_companyId()).append("' ");
				} else if ("02".equals(companyType)) {//代理商
					sb.append(" and iei.agent_id= '").append(req.getUinfo().getCompanyId()).append("' ");
				} else {//管理员
					sb.append(" and iei.manufacture_id='MA3NU7QX1' ");
				}
			} else {//演示账号默认德尚
				sb.append(" and iei.manufacture_id='MA3NU7QX1' ");
			}
			sb.append(StrUtil.isEmpty(xzqhdm) ? "" : "and tx.xzqhdm like concat('" + xzqhdm + "','%')");
			List<Map<String, Object>> list2 = queryForList(conn_ope, sb.toString(), new Object[]{});
			retMap.put("devices", list2);
			System.out.println("devices size:" + list2.size());

			res.setData(retMap);
			res.setRetCode(0);
			res.setRetMessage("OK");
		} finally {
			if (conn != null) {
				conn.close();
			}
			if (conn_ope != null) {
				conn_ope.close();
			}
		}
		return res;
	}

	public PageData getDeviceDataDetailsByProvince(DataReq req) throws Exception {
		PageData res = new PageData();
		res.setRetCode(9);
		res.setRetMessage("查询设备详情数据失败");
		StringBuffer sb = new StringBuffer();
		Connection conn = null;
		Map<String, Object> retMap = new HashMap<>();
		try {
			conn = DBUtils.getDBConn_Ope();
			sb.append("select count(iei.sb_id)                                     as total,\n" +
					"       count(ind2.is_online = 0 or null)                       online,\n" +
					"       count(iei.sb_id) - count(ind2.is_online = 0 or null) as offline,\n" +
					"       count(ind2.is_warn = 1 or null) as warnCount,\n" +
					"       count(ind2.fault_code is not null or null) as faultCount\n" +
					"from nes_ope_prod.is_equipment_info iei\n" +
					"inner join is_install_info iii on iii.dd_id = iei.dd_id\n" +
					"left join nes_ope_prod.is_norm_data ind2 on ind2.dtu_id = iei.wx_id and ind2.sb_id = iei.sb_id\n" +
					"where iei.sfsc = 0 \n");
			// 用户信息中获取用户的公司类型
			String companyType = req.getUinfo().getCompanyType();
			// 根据公司类型、公司ID，查询设备数据
			if (!Globals.DEMO_COMPANYID.equals(req.getUinfo().getCompanyId())) {//不是演示账号
				if ("01".equals(companyType)) {//厂商
					sb.append(" and iei.manufacture_id= '").append(req.getUinfo().getNes_companyId()).append("' ");
				} else if ("02".equals(companyType)) {//代理商
					sb.append(" and iei.agent_id= '").append(req.getUinfo().getCompanyId()).append("' ");
				} else {//管理员
					sb.append(" and iei.manufacture_id='MA3NU7QX1' ");
				}
			} else {//演示账号默认德尚
				sb.append(" and iei.manufacture_id='MA3NU7QX1' ");
			}
			sb.append(" and substr(iii.xzqh, 1, 2) = '" + req.getReqData().get("province") + "'");
			List<Map<String, Object>> list = queryForList(conn, sb.toString(), new Object[]{});
			retMap.put("data", list);

			sb.delete(0, sb.length());
			sb.append("select MONTH(iei.cjsj) as month, count(iei.sb_id) as deviceCount\n" +
					"from nes_ope_prod.is_equipment_info iei\n" +
					"inner join is_install_info iii on iii.dd_id = iei.dd_id\n\n" +
					"where iei.sfsc = 0\n" +
					"  and year(iei.cjsj) = year(now()) \n");
			sb.append(" and substr(iii.xzqh, 1, 2) = '" + req.getReqData().get("province") + "'");
			// 根据公司类型、公司ID，查询设备数据
			if (!Globals.DEMO_COMPANYID.equals(req.getUinfo().getCompanyId())) {//不是演示账号
				if ("01".equals(companyType)) {//厂商
					sb.append(" and iei.manufacture_id= '").append(req.getUinfo().getNes_companyId()).append("' ");
				} else if ("02".equals(companyType)) {//代理商
					sb.append(" and iei.agent_id= '").append(req.getUinfo().getCompanyId()).append("' ");
				} else {//管理员
					sb.append(" and iei.manufacture_id='MA3NU7QX1' ");
				}
			} else {//演示账号默认德尚
				sb.append(" and iei.manufacture_id='MA3NU7QX1' ");
			}
			sb.append(" group by MONTH(iei.cjsj)");
			List<Map<String, Object>> list1 = queryForList(conn, sb.toString(), new Object[]{});
			Long[] month = new Long[12];
			for (int i = 0; i <12; i++) {
				month[i] = 0L;
			}
			if (CollUtil.isNotEmpty(list1)) {
				for (Map<String, Object> map : list1) {
					month[(int) map.get("month")-1] = (Long) map.get("deviceCount");
				}
			}
			retMap.put("month", month);

			res.setData(retMap);
			res.setRetCode(0);
			res.setRetMessage("OK");
		} finally {
			if (conn != null) {
				conn.close();
			}
		}
		return res;
	}

	public PageData getClueTranslateAndSalesRanking(DataReq req) throws Exception {
		PageData res = new PageData();
		res.setRetCode(9);
		res.setRetMessage("查询设备详情数据失败");
		StringBuffer sb = new StringBuffer();
		Connection conn = null;
		Map<String, Object> retMap = new HashMap<>();
		try {
			conn = DBUtils.getJNDIDBConn();
			sb.append("select year(c.createTime) as year, count(c.uuid) as total, count(c.clueowner is not null or null) translate, " +
                    "format((count(c.clueowner is not null or null) / count(c.uuid)) * 100, 2) as rate\n" +
					"from clue c\n" +
					"where c.isdelete = 0\n" +
					"     and c.cluebelongtocompany = '" + req.getUinfo().getCompanyId() + "'\n" +
					"group by year(c.createTime) order by year(c.createTime) desc");
			List<Map<String, Object>> list = queryForList(conn, sb.toString(), new Object[]{});
			if (CollUtil.isNotEmpty(list)) {
				retMap.put("clue", list);
			}

			sb.delete(0, sb.length());
			String companyType = req.getUinfo().getCompanyType();
			sb.append("select tc.companyName, tc.shortname \n from t_agent_facturer taf\n");
			// 01-厂商，02-代理商
			if (companyType.equals("01")) {
				sb.append(" inner join t_company tc on tc.companyId = taf.agent_id\n");
				sb.append(" where taf.deleted = 0 and taf.facturer_id = '" + req.getUinfo().getCompanyId() + "'");
			} else {
				sb.append(" inner join t_company tc on tc.companyId = taf.facturer_id\n");
				sb.append(" where taf.deleted = 0 and taf.agent_id = '" + req.getUinfo().getCompanyId() + "'");
			}
			List<Map<String, Object>> companyList = queryForList(conn, sb.toString(), new Object[]{});
			if (CollUtil.isNotEmpty(companyList)) {
				// 年份设备数量
				HashMap<String, Long[]> yearDeviceCountMap = new HashMap<>();
				for (Map<String, Object> map : companyList) {
					// 1位-本年， 2位-去年
					Long[] arr = new Long[]{0L,0L};
					yearDeviceCountMap.put((String) map.get("companyName"), arr);
				}


				// 查询本年设备量
				sb.delete(0, sb.length());
				sb.append("SELECT b.companyName, count(iei.sb_id) as deviceCount\n" +
						"from nes_ope_prod.is_equipment_info iei\n");
				if (companyType.equals("01")) {
					sb.append("INNER JOIN t_company b on iei.agent_id = b.companyId\n" +
							"where iei.sfsc = 0 and year(iei.cjsj) = year(now())\n" +
							"  and iei.manufacture_id = '" + req.getUinfo().getNes_companyId() + "'\n" +
							"group by b.companyName");
				} else {
					sb.append("INNER JOIN t_company b on iei.manufacture_id = b.nes_companyId\n" +
							"where iei.sfsc = 0 and year(iei.cjsj) = year(now())\n" +
							"  and iei.agent_id = '" + req.getUinfo().getCompanyId() + "'\n" +
							"group by b.companyName");
				}
				List<Map<String, Object>> currentYearDeviceCount = queryForList(conn, sb.toString(), new Object[]{});
				if (CollUtil.isNotEmpty(currentYearDeviceCount)) {
					for (Map<String, Object> map : currentYearDeviceCount) {
						if (yearDeviceCountMap.containsKey((String) map.get("companyName"))) {
							Long[] companyData = yearDeviceCountMap.get((String) map.get("companyName"));
							companyData[0] = (Long) map.get("deviceCount");
							yearDeviceCountMap.put((String) map.get("companyName"), companyData);
						}
					}
				}


				// 查询去年设备量
				sb.delete(0, sb.length());
				sb.append("SELECT b.companyName, count(iei.sb_id) as deviceCount\n" +
						"from nes_ope_prod.is_equipment_info iei\n");
				if (companyType.equals("01")) {
					sb.append("INNER JOIN t_company b on iei.agent_id = b.companyId\n" +
							"where iei.sfsc = 0 and year(iei.cjsj) = year(now())-1\n" +
							"  and iei.manufacture_id = '" + req.getUinfo().getNes_companyId() + "'\n" +
							"group by b.companyName");
				} else {
					sb.append("INNER JOIN t_company b on iei.manufacture_id = b.nes_companyId\n" +
							"where iei.sfsc = 0 and year(iei.cjsj) = year(now())-1\n" +
							"  and iei.agent_id = '" + req.getUinfo().getCompanyId() + "'\n" +
							"group by b.companyName");
				}
				List<Map<String, Object>> lastYearDeviceCount = queryForList(conn, sb.toString(), new Object[]{});
				if (CollUtil.isNotEmpty(lastYearDeviceCount)) {
					for (Map<String, Object> map : lastYearDeviceCount) {
						if (yearDeviceCountMap.containsKey((String) map.get("companyName"))) {
							Long[] companyData = yearDeviceCountMap.get((String) map.get("companyName"));
							companyData[1] = (Long) map.get("deviceCount");
							yearDeviceCountMap.put((String) map.get("companyName"), companyData);
						}
					}
				}

				List<HashMap<String, Object>> sortList = new ArrayList<>();
				for (String companyName : yearDeviceCountMap.keySet()) {
					Long[] deviceCount = yearDeviceCountMap.get(companyName);
//					System.out.println(deviceCount[0] + " " + deviceCount[1]);
					Long a = deviceCount[1] == 0 ? 1 : deviceCount[1];
					Double rate = Double.valueOf((deviceCount[0] - deviceCount[1]) / a);
					String  str = String.format("%.2f",rate);
					rate = Double.parseDouble(str);

//					System.out.println(companyName + "=" + rate);
					// 组装返回数据，添加到list中，进行排序
					HashMap<String, Object> companyRateMap = new LinkedHashMap<>();
					companyRateMap.put("name", companyName);
					companyRateMap.put("rate", rate);
					companyRateMap.put("count", deviceCount[0]);
					sortList.add(companyRateMap);
				}

				// 排序
				Collections.sort(sortList, new Comparator<HashMap<String, Object>>() {
					@Override
					public int compare(HashMap<String, Object> o1, HashMap<String, Object> o2) {
						Double rate1 = (Double) o1.get("rate");
						Double rate2 = (Double) o2.get("rate");
						return rate2.compareTo(rate1); // 倒序排列
					}
				});

				retMap.put("deviceIncreamRateRank", sortList);
			}


			res.setData(retMap);
			res.setRetCode(0);
			res.setRetMessage("OK");
		} finally {
			if (conn != null) {
				conn.close();
			}
		}
		return res;
	}

}
